"""
Case Type   : Ubtree
Case Name   : 测试ubtree恢复_二级分区表_删除二级分区数据后回滚
Create At   : 2025-10-11
Owner       : sungang14
Description :
    1:建表;
    2:建索引时指定PCR
    3:查看索引类型
    4:插入数据
    5:查询表、索引大小
    6:事务操作
    7:查询表、索引大小
    8:清理环境
Expect      :
    1、成功
    2、成功
    3、成功
    4、成功
    5、成功
    6、成功
    7、与事务操作前一致
    8、成功
History     :
"""
import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

logger = Logger()


class SlowSQLTestCase(unittest.TestCase):
    def setUp(self):
        logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.comsh = CommonSH('PrimaryDbUser')
        status = self.comsh.get_db_cluster_status('status')
        self.assertTrue(status)
        self.table_name = 't_ubtree_0094'
        self.prepare_data_01 = f"""drop table if exists t_ubtree_0094 cascade;
                               create table t_ubtree_0094 (
                                    id int,
                                    sale_date date,
                                    region varchar(20),
                                    amount numeric(10,2)
                                )
                                with (storage_type=ustore)
                                partition by range (sale_date)
                                subpartition by list (region)
                                (
                                    partition p_2022 values less than ('2023-01-01') (
                                        subpartition p_2022_east values ('east'),
                                        subpartition p_2022_west values ('west')
                                    ),
                                    partition p_2023 values less than ('2024-01-01') (
                                        subpartition p_2023_east values ('east'),
                                        subpartition p_2023_west values ('west')
                                    )
                                );"""
        self.prepare_data_02 = f"create index i_ubtree_0094 on t_ubtree_0094 using ubtree(sale_date, region) with (index_type=pcr);"
        self.prepare_data_03 = f"select relkind, reloptions from pg_class where relname like 'i_ubtree_0094%';"
        self.prepare_data_04 = f"insert into t_ubtree_0094 select g,case when g%2=0 then '2023-01-15'::date else " \
                               f"'2022-12-15'::date end,case when random() < 0.5 then 'east' else 'west' end," \
                               f"(random()*10000)::numeric(10,2) from generate_series(1,1000) as g;"
        self.prepare_data_05_1 = f"select count(*) from {self.table_name};"
        self.prepare_data_05_2 = f"select checksum({self.table_name}::text) from {self.table_name};"
        self.prepare_data_05_3 = f"select pg_size_pretty(pg_relation_size('i_ubtree_0094')) as init_index_size;"
        self.prepare_data_05_4 = f"explain (costs off) select * from t_ubtree_0094 where sale_date between '2023-01-01' and '2023-12-31' and region='west';"
        self.prepare_data_06 = f"begin;" \
                               f"alter table t_ubtree_0094 truncate subpartition p_2023_west;" \
                               f"rollback;"
        self.prepare_data_08 = f"drop table if exists {self.table_name} cascade;"

    def test_slowsql(self):
        text = '--step1:建表;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_01)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, data_cmd, '执行失败' + text)

        text = "--step2:建索引时指定PCR;expect:成功--"
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_02)
        logger.info(data_cmd)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS_MSG, data_cmd, '执行失败' + text)

        text = '--step3:查看索引类型;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_03)
        logger.info(data_cmd)
        self.assertIn(" I       | {index_type=pcr,storage_type=ustore}", data_cmd, '执行失败' + text)

        text = '--step4:插入数据;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_04)
        logger.info(data_cmd)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, data_cmd, '执行失败' + text)

        text = '--step5: 查询表、索引大小;expect:成功--'
        logger.info(text)
        data_cmd_01 = self.comsh.execut_db_sql(self.prepare_data_05_1)
        logger.info(data_cmd_01)
        re_msg_01 = data_cmd_01.splitlines()[2].strip()
        logger.info(re_msg_01)
        data_cmd_02 = self.comsh.execut_db_sql(self.prepare_data_05_2)
        logger.info(data_cmd_02)
        re_msg_02 = data_cmd_02.splitlines()[2].strip()
        logger.info(re_msg_02)
        data_cmd_03 = self.comsh.execut_db_sql(self.prepare_data_05_3)
        logger.info(data_cmd_03)
        re_msg_03 = data_cmd_03.splitlines()[2].strip()
        logger.info(re_msg_03)

        text = '--step6: 事务操作;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_06)
        logger.info(data_cmd)
        self.assertIn(self.constant.ROLLBACK_MSG, data_cmd, '执行失败' + text)

        text = '--step7: 查看表、索引大小;expect:与事务操作前一致--'
        logger.info(text)
        data_cmd_01after = self.comsh.execut_db_sql(self.prepare_data_05_1)
        logger.info(data_cmd_01after)
        re_msg_01after = data_cmd_01after.splitlines()[2].strip()
        logger.info(re_msg_01after)
        data_cmd_02after = self.comsh.execut_db_sql(self.prepare_data_05_2)
        logger.info(data_cmd_02after)
        re_msg_02after = data_cmd_02after.splitlines()[2].strip()
        logger.info(re_msg_02after)
        data_cmd_03after = self.comsh.execut_db_sql(self.prepare_data_05_3)
        logger.info(data_cmd_03after)
        re_msg_03after = data_cmd_03after.splitlines()[2].strip()
        logger.info(re_msg_03after)
        data_cmd_04after = self.comsh.execut_db_sql(self.prepare_data_05_4)
        logger.info(data_cmd_04after)
        self.assertEqual(re_msg_01, re_msg_01after, '执行失败' + text)
        self.assertEqual(re_msg_02, re_msg_02after, '执行失败' + text)
        self.assertEqual(re_msg_03, re_msg_03after, '执行失败' + text)
        self.assertIn(self.constant.EXPLAIN_SUCCESS_MSG, data_cmd_04after, '执行失败' + text)

    def tearDown(self):
        text = '--step8:清理环境;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_08)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        logger.info(f'-----{os.path.basename(__file__)} end-----')
